﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;

namespace Clmp.Data.DBEntity
{
    public class DataContainer : IDisposable
    {
        /// <summary>
        /// 创建数据容器
        /// </summary>
        public DataContainer()
        {
            IContainerConfiguration config = new ContainerConfiguration();
            connectionString = config.ConnectionString;
            Init();
        }
        /// <summary>
        /// 创建数据容器
        /// </summary>
        /// <param name="connectionString">数据库连接字符串</param>
        public DataContainer(string connectionString)
        {
            this.connectionString = connectionString;
            Init();
        }

        #region 私有字段

        private string connectionString;
        private SqlConnection con;
        private IMapping mapping;

        #endregion 私有字段

        private void Init()
        {
            if (!TransactionContext.InTransaction)
            {
                con = new SqlConnection(this.connectionString);
                //TODO:DEBUG
                //TransactionContext.Debug("\t create conn.");
            }
            else
            {
                con = TransactionContext.GetConnection(connectionString);
            }
            mapping = new Mapping();
        }

        #region 连接相关
        /// <summary>
        /// 打开数据连接
        /// </summary>
        /// <returns>this</returns>
        public DataContainer OpenConnection()
        {
            if (con.State == System.Data.ConnectionState.Closed)
            {
                con.Open();
            }
            return this;
        }
        /// <summary>
        /// 关闭数据连接(事务中关闭无效)
        /// </summary>
        /// <returns>this</returns>
        public DataContainer CloseConnection()
        {
            if (!TransactionContext.InTransaction)
            {
                if (con.State != System.Data.ConnectionState.Closed)
                {
                    con.Close();
                }
            }
            return this;
        }
        #endregion 连接相关


        #region 实体增删改查
        /// <summary>
        /// 添加数据
        /// </summary>
        /// <typeparam name="T">实体类型</typeparam>
        /// <param name="entity">实体对象</param>
        /// <returns>主键值(自增ID)</returns>
        public object Store<T>(T entity) where T : Entity
        {
            SqlCommand com = CreateCommend(null);
            string pkName = mapping.GetTableName<T>();
            StringBuilder sql = new StringBuilder("INSERT INTO [").Append(pkName).Append("]");
            StringBuilder props = new StringBuilder(" (");
            StringBuilder propVals = new StringBuilder(" (");

            var propDic = entity.GetPropertyDictionary();
            if (propDic == null || propDic.Count == 0)
                throw new DataContainerException("指定对象不包含任何属性值。");

            foreach (var prop in propDic)
            {
                props.Append(",[").Append(prop.Key).Append("]");
                propVals.Append(",@").Append(prop.Key);

                com.Parameters.Add(GetInputParameter("@" + prop.Key, prop.Value));
            }

            props.Remove(2, 1).Append(") ");
            propVals.Remove(2, 1).Append(") ");

            sql.Append(props.ToString())
                .Append(" output  inserted.").Append(mapping.GetTablePrimaryKey<T>())
                .Append(" VALUES").Append(propVals.ToString());

            com.CommandText = sql.ToString();

            object newPk = com.ExecuteScalar();
            entity.SetPropertyValue(mapping.GetTablePrimaryKey<T>(), newPk);
            return newPk;
        }

        /// <summary>
        /// 更新数据
        /// </summary>
        /// <typeparam name="T">实体类型</typeparam>
        /// <param name="entity">实体对象</param>
        public int Update<T>(T entity) where T : Entity
        {
            SqlCommand com = CreateCommend(null);

            StringBuilder sql = new StringBuilder("UPDATE [").Append(mapping.GetTableName<T>()).Append("] SET ");
            StringBuilder props = new StringBuilder();
            string primaryKey = mapping.GetTablePrimaryKey<T>();

            foreach (var prop in entity.GetPropertyDictionary())
            {
                if (prop.Key != primaryKey)
                {
                    props.Append(",[").Append(prop.Key).Append("]=@").Append(prop.Key);
                    com.Parameters.Add((GetInputParameter("@" + prop.Key, prop.Value)));
                }
            }

            props.Remove(0, 1);

            sql.Append(props.ToString())
                .Append(" WHERE [" + primaryKey + "]=@ID");
            com.Parameters.Add(new SqlParameter("@ID", entity.GetPropertyValue<int>(primaryKey)));

            com.CommandText = sql.ToString();

            return com.ExecuteNonQuery();
        }

        /// <summary>
        /// 删除数据
        /// </summary>
        /// <typeparam name="T">实体类型</typeparam>
        /// <param name="id">要删除的实体主键值</param>
        public int Delete<T>(int id) where T : Entity
        {
            string sql = "DELETE FROM [" + mapping.GetTableName<T>() + "] WHERE [" + mapping.GetTablePrimaryKey<T>() + "]=@ID";
            SqlCommand com = CreateCommend(sql);
            com.Parameters.Add((GetInputParameter("@ID", id)));
            return com.ExecuteNonQuery();
        }
        /// <summary>
        /// 获取实体对象
        /// </summary>
        /// <typeparam name="T">实体类型</typeparam>
        /// <param name="id">实体主键值</param>
        /// <returns>实体对象</returns>
        public T GetOne<T>(int id) where T : Entity
        {
            string sql = "SELECT TOP 1 * FROM [" + mapping.GetTableName<T>() + "] WHERE [" + mapping.GetTablePrimaryKey<T>() + "]=@ID";
            SqlCommand com = CreateCommend(sql);
            com.Parameters.Add(GetInputParameter("@ID", id));
            using (SqlDataReader reader = com.ExecuteReader())
            {
                if (reader != null)
                    if (reader.Read())
                    {
                        T model = EntityFactory<T>.CreateEntity();
                        for (int i = 0; i < reader.FieldCount; i++)
                        {
                            model.SetPropertyValue(reader.GetName(i), reader.GetValue(i));
                        }
                        return model;
                    }
            }
            return null;
        }

        #endregion 增删改


        #region 执行Sql语句
        /// <summary>
        /// 执行sql语句，返回受影响的行数
        /// </summary>
        /// <param name="sqlStr">Sql语句</param>
        /// <param name="pars">Sql参数</param>
        /// <returns></returns>
        public int ExecuteNonQuery(string sqlStr, params object[] pars)
        {
            SqlCommand com = CreateCommend(sqlStr);
            var outputInfos = SetCommandParsAndReturnOutputs(com, sqlStr, pars);
            int flag = com.ExecuteNonQuery();
            SetOutputValues(com, outputInfos);
            return flag;
        }

        /// <summary>
        /// 执行查询
        /// </summary>
        /// <typeparam name="T">实体类型</typeparam>
        /// <param name="sqlStr">Sql语句</param>
        /// <param name="pars">Sql参数</param>
        /// <returns>实体集合</returns>
        public List<T> Query<T>(string sqlStr, params object[] pars) where T : Entity
        {
            SqlCommand com = CreateCommend(sqlStr);
            var outputInfos = SetCommandParsAndReturnOutputs(com, sqlStr, pars);

            List<T> list = new List<T>();
            using (SqlDataReader reader = com.ExecuteReader())
            {
                if (reader != null)
                    while (reader.Read())
                    {
                        T model = EntityFactory<T>.CreateEntity();
                        for (int i = 0; i < reader.FieldCount; i++)
                        {
                            model.SetPropertyValue(reader.GetName(i), reader.GetValue(i));
                        }
                        list.Add(model);
                    }
            }
            SetOutputValues(com, outputInfos);
            return list;
        }
        /// <summary>
        /// 执行Sql语句，返回查询结果第一行第一列的值
        /// </summary>
        /// <param name="sqlStr">Sql语句</param>
        /// <param name="pars">Sql参数</param>
        /// <returns>查询结果第一行第一列的值</returns>
        public object ExecuteScalar(string sqlStr, params object[] pars)
        {
            SqlCommand com = CreateCommend(sqlStr);
            var outputInfos = SetCommandParsAndReturnOutputs(com, sqlStr, pars);
            object rtnVal = com.ExecuteScalar();
            SetOutputValues(com, outputInfos);
            return rtnVal;
        }
        /// <summary>
        /// 执行Sql语句，返回第一行第一列的值
        /// </summary>
        /// <typeparam name="T">返回值类型</typeparam>
        /// <param name="sqlStr">Sql语句</param>
        /// <param name="pars">Sql参数</param>
        /// <returns>查询结果第一行第一列的值</returns>
        public T ExecuteScalar<T>(string sqlStr, params object[] pars)
        {
            object val = ExecuteScalar(sqlStr, pars);
            if (val != null)
                return (T)val;
            return default(T);
        }
        #endregion 执行Sql语句


        #region Private
        /// <summary>
        /// 生成数据库参数
        /// </summary>
        /// <param name="key">参数名称</param>
        /// <param name="value">参数值</param>
        /// <returns></returns>
        private SqlParameter GetInputParameter(string key, object value)
        {
            if (value == null)
                return new SqlParameter(key, DBNull.Value);
            var type = GetDbType(value);
            var par = new SqlParameter(key,type);
            par.Value = value;
            return par;
        }

        /// <summary>
        /// 获取数据库输出参数
        /// </summary>
        /// <param name="outputInfo"></param>
        /// <returns></returns>
        private SqlParameter GetOutputParameter(OutputInfo outputInfo)
        {
            var par = new SqlParameter(outputInfo.ParameterName, GetDbOutputType(outputInfo.Output.GetValueType())) { Direction = System.Data.ParameterDirection.Output };
            if (outputInfo.Output.GetValueType() == typeof(string))
                par.Size = 8000;
            return par;
        }

        /// <summary>
        /// 设置Command对象的数据库参数
        /// </summary>
        /// <param name="com">Command对象</param>
        /// <param name="sqlStr">Sql语句</param>
        /// <param name="pars">数据库参数</param>
        /// <returns>输出参数信息集合</returns>
        private List<OutputInfo> SetCommandParsAndReturnOutputs(SqlCommand com, string sqlStr, object[] pars)
        {
            List<OutputInfo> outputs = new List<OutputInfo>();
            if (pars != null && pars.Length > 0)
            {
                System.Text.RegularExpressions.Regex regex = new System.Text.RegularExpressions.Regex("(?<par>@[\\w]+)");
                System.Text.RegularExpressions.MatchCollection matches = regex.Matches(sqlStr);

                if (matches.Count != pars.Count())
                    throw new System.ArgumentException("Sql语句与提供的数据库参数不匹配。");

                for (int i = 0; i < matches.Count; i++)
                {
                    object par = pars[i];
                    string parName = matches[i].Result("${par}");
                    if (par is IOutput)
                    {
                        //输出参数
                        IOutput output = (IOutput)par;
                        OutputInfo outputInfo = new OutputInfo() { Output = output, ParameterName = parName };
                        com.Parameters.Add(GetOutputParameter(outputInfo));
                        outputs.Add(outputInfo);
                    }
                    else
                    {
                        com.Parameters.Add(GetInputParameter(parName, pars[i]));
                    }
                }
            }
            return outputs;
        }

        /// <summary>
        /// 设置返回值
        /// </summary>
        /// <param name="com"></param>
        /// <param name="outputInfos"></param>
        private void SetOutputValues(SqlCommand com, List<OutputInfo> outputInfos)
        {
            foreach (OutputInfo output in outputInfos)
                output.Output.Value = com.Parameters[output.ParameterName].Value;
        }

        /// <summary>
        /// 生成Commend对象
        /// </summary>
        /// <param name="sqlStr">Sql语句</param>
        /// <returns>Commend对象</returns>
        private SqlCommand CreateCommend(string sqlStr)
        {
            SqlCommand com = new SqlCommand();

            if (!string.IsNullOrEmpty(sqlStr))
            {
                //存贮过程
                if (sqlStr.IndexOf(' ') < 0)
                {
                    //存贮过程
                    com.CommandType = System.Data.CommandType.StoredProcedure;
                    sqlStr = sqlStr.Substring(0, sqlStr.IndexOf("("));
                }
                com.CommandText = sqlStr;
            }

            //获取线程上下文Connection、Transaction
            if (TransactionContext.InTransaction)
            {
                com.Transaction = TransactionContext.DbTransaction;
            }
            com.Connection = con;

            return com;
        }

        /// <summary>
        /// 获取数据库数据类型
        /// </summary>
        /// <param name="value">参数值</param>
        /// <returns>SqlDbType 类型</returns>
        private static System.Data.SqlDbType GetDbType(object value)
        {
            Type type = value.GetType();
            switch (type.Name)
            {
                case "Int32": return System.Data.SqlDbType.Int;
                case "String": return ((string)value).Length > 255 ? System.Data.SqlDbType.NText : System.Data.SqlDbType.NVarChar;
                case "Boolean": return System.Data.SqlDbType.Bit;
                case "Single": return System.Data.SqlDbType.Float;
                case "DateTime": return System.Data.SqlDbType.DateTime;
                default:
                    if (type.IsEnum) return System.Data.SqlDbType.Int;
                    if (type.Name.IndexOf("Nullable") >= 0)
                    {
                        //可空类型
                        var rtnType = type.GetMethod("get_Value").ReturnType;
                        return GetDbType(rtnType);
                    }
                    throw new Exception("不支持的数据类型");
            }
        }

        private static System.Data.SqlDbType GetDbOutputType(Type type)
        {
            switch (type.Name)
            {
                case "Int32": return System.Data.SqlDbType.Int;
                case "String": return System.Data.SqlDbType.VarChar;
                case "Boolean": return System.Data.SqlDbType.Bit;
                case "Single": return System.Data.SqlDbType.Float;
                case "DateTime": return System.Data.SqlDbType.DateTime;
                default:
                    if (type.IsEnum) return System.Data.SqlDbType.Int;
                    if (type.Name.IndexOf("Nullable") >= 0)
                    {
                        //可空类型
                        var rtnType = type.GetMethod("get_Value").ReturnType;
                        return GetDbType(rtnType);
                    }
                    throw new Exception("不支持的数据类型");
            }
        }

        #endregion Private


        #region 数据库创建时所需

        /// <summary>
        /// 创建数据表
        /// </summary>
        /// <typeparam name="T">实体类型</typeparam>
        public void CreateTable<T>() where T : Entity
        {
            StringBuilder sql = new StringBuilder("CREATE TABLE [").Append(mapping.GetTableName<T>()).Append("] (\n");

            var pors = typeof(T).GetProperties();
            foreach (var por in pors)
            {
                ColumnAttribute columnInfo;
                object columnInfoObj = por.GetCustomAttributes(typeof(ColumnAttribute), false).FirstOrDefault();
                if (columnInfoObj == null)
                    columnInfo = new ColumnAttribute(por.Name);
                else
                    columnInfo = (ColumnAttribute)columnInfoObj;

                if (por.GetSetMethod().IsVirtual)
                {
                    if (columnInfo.Name == mapping.GetTablePrimaryKey<T>())
                    {
                        sql.Append("[").Append(columnInfo.Name).Append("] ").Append(GetDbTypeString(por.PropertyType)).Append(" IDENTITY(1,1) PRIMARY KEY NOT NULL,\n");
                    }
                    else
                    {
                        sql.Append("[").Append(columnInfo.Name).Append("] ").Append(GetDbTypeString(por.PropertyType)).Append(",\n");
                    }
                }
            }

            sql.Remove(sql.Length - 2, 1);
            sql.Append("\n)");

            SqlCommand com = CreateCommend(sql.ToString());
            if (com.ExecuteNonQuery() == 0)
                throw new System.Data.DBConcurrencyException("创建数据库表失败。");
        }

        private static System.Data.SqlDbType GetDbTypeByType(Type type)
        {
            switch (type.Name)
            {
                case "Int32": return System.Data.SqlDbType.Int;
                case "String": return System.Data.SqlDbType.NVarChar;
                case "Boolean": return System.Data.SqlDbType.Bit;
                case "Single": return System.Data.SqlDbType.Float;
                case "DateTime": return System.Data.SqlDbType.DateTime;
                default:
                    if (type.IsEnum) return System.Data.SqlDbType.Int;
                    if (type.Name.IndexOf("Nullable") >= 0)
                    {
                        //可空类型
                        var rtnType = type.GetMethod("get_Value").ReturnType;
                        return GetDbTypeByType(rtnType);
                    }
                    throw new Exception("不支持的数据类型");
            }
        }

        private static string GetDbTypeString(Type type)
        {
            var dbType = GetDbTypeByType(type);
            switch (dbType)
            {
                case System.Data.SqlDbType.NVarChar: return "[" + System.Data.SqlDbType.NVarChar.ToString().ToLower() + "]" + "(255)";
                default:
                    return "[" + dbType.ToString().ToLower() + "]";
            }
        }
        #endregion 数据库创建时所需


        #region IDisposable 成员

        public void Dispose()
        {
            CloseConnection();
        }

        #endregion
    }
}
